<?php
/**
 * Created by PhpStorm.
 * User: xuanbang.wen
 * Date: 2020/2/18
 * Time: 10:08
 */

namespace App\Web\Model;


use App\Service\ExternalAPI\VSApi;
use App\Service\Mailer;
use App\Web\Facades\UserAuth;
use EchoPHP\Facades\Db;
use EchoPHP\Facades\Log;
use EchoPHP\Facades\Validation;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Facades\UrlCrypt;
use PhpOffice\PhpSpreadsheet\IOFactory;


class VideoInterview extends Model {
    public $roomStatusMap = [
        '01' => '待审核',
        '02' => '审核通过',
        '03' => '审核不通过',
        '06' => '已取消',
    ];
    public $StatusMap = [
        '01' => '待审核',
        '03' => '审核通过',
        '05' => '审核不通过',
        '06' => '删除',
    ];

    private $recordVideoType = 'ia|iv|sa|sv';

    /**
     * Notes:创建房间申请
     * @param $params
     * @return int
     * @throws \Throwable
     */
    public function createRoomApply($params) {
        $user = UserAuth::getLoginInfo();
        $hasRight = $this->chkInterviewerRight($params['projectid']);
        if (!$hasRight) {
            return 0;
        }

        $sql = 'select subid from Project where projectid = ?';
        $subId = Db::selectOne($sql, [$params['projectid']]);
        if (!$subId) {
            return 0;
        }
        $subId = $subId['subid'];
        if (!is_array($params['interviewerids']) || !$params['interviewerids']) {
            return 0;
        }
        $sql = 'select id as interviewerid, cname, mobilephone, email from CustomerAccount where isinterviewer = ? and id in ( ' . mergeArrSql($params['interviewerids']) . ' )';;
        $interviewerInfo = Db::select($sql, array_merge([1],$params['interviewerids']));
        if (!$interviewerInfo) {
            return 0;
        }
        return Db::connect('jyrpo_pdo')->transaction(function () use ($params, $user, $subId, $interviewerInfo) {
            $sql = 'insert into Room (subid, projectid, roomname, starttime, endtime,status, linktype, creator, updator, teamcode, istemp, createdate, updatedate) values (?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
            $var = [
                $subId,
                $params['projectid'],
                $params['roomname'],
                $params['starttime'],
                $params['endtime'],
                '01',
                $params['linktype'],
                $user['userid'],
                $user['userid'],
                $user['teamcode'],
                $params['istemp'],
            ];
            Db::connect('jyrpo_pdo')->insert($sql, $var);
            $roomId = Db::connect('jyrpo_pdo')->getLastInsertId();
            $sql = 'insert into RoomInterviewer (roomid, subid, projectid, interviewerid, cname, mobilephone, email, starttime, endtime, master, status, creator, updator, createdate, updatedate) values';
            $values = $var = $tmpVar = [];
            foreach ($interviewerInfo as $interview) {
                $values[] = '(?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $tmpVar = [
                    $roomId,
                    $subId,
                    $params['projectid'],
                    $interview['interviewerid'],
                    $interview['cname'],
                    $interview['mobilephone'],
                    $interview['email'],
                    $params['starttime'],
                    $params['endtime'],
                    1,
                    '01',
                    $user['userid'],
                    $user['userid'],
                ];
                $var = array_merge($var, $tmpVar);
            }
            $sql .= implode($values, ',');
            Db::connect('jyrpo_pdo')->insert($sql, $var);
            return $roomId;
        });
    }

    /**
     * Notes:创建房间审核
     * @param $roomId
     * @param $status
     * @param $reason
     * @return array|mixed
     * @throws \Throwable
     */
    public function createRoomCheck($roomId, $status, $reason) {
        $user = UserAuth::getLoginInfo();
        $sql = 'select status,starttime from Room where roomid = ?';
        $var = [$roomId];
        if ($user['issuper'] != 1) {
            $sql .= ' and teamcode like ? ';
            $var[] = substr($user['teamcode'], 0, 5) . '%';
        }
        $room = Db::selectOne($sql, $var);
        if (!$room || $room['status'] != '01') {
            return [
                'status' => false,
                'message' => '无效房间'
            ];
        }
        if ($status != 0 && $room['starttime'] < date('Y-m-d H:i:s')) {
            return [
                'status' => false,
                'message' => '已超时不可审批通过'
            ];
        }
        if ($status == 0) {
            if (!Validation::validateMaxLength('', $reason, 200)) {
                return [
                    'status' => false,
                    'message' => '内容不可超过200字'
                ];
            }
            return Db::connect('jyrpo_pdo')->transaction(function () use ($roomId, $user, $reason) {
                $sql = 'update Room set updator = ?, updatedate = NOW(), checkuid = ?, checkdate = NOW(), status = ?, reason = ? where roomid = ?';
                $var = [
                    $user['userid'],
                    $user['userid'],
                    '03',
                    $reason,
                    $roomId
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);
                $sql = 'update RoomInterviewer set updator = ?, checkuid = ?, status = ?, checkdate = NOW(), updatedate = NOW() where roomid = ?';
                $var = [
                    $user['userid'],
                    $user['userid'],
                    '05',
                    $roomId
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);
                $sql = 'update RoomJobseeker set updator = ?, checkuid = ?, status = ?, checkdate = NOW(), updatedate = NOW()where roomid = ?';
                $var = [
                    $user['userid'],
                    $user['userid'],
                    '05',
                    $roomId
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);
                return [
                    'status' => true,
                    'message' => '审核完成'
                ];
            });
        }
        $sql = 'select roomname, linktype, starttime, endtime, projectid, subid from Room where roomid = ?';
        $roomInfo = Db::selectOne($sql, [$roomId]);
        $sql = 'select crmsubid,left(subname, 100) as subname from CustomerSub where subid = ?';
        $subInfo = Db::selectOne($sql, [$roomInfo['subid']]);
        $roomInfo['crmsubid'] = $subInfo['crmsubid'];
        $roomInfo['subname'] = $subInfo['subname'];
        $sql = 'select cname as interviewername,master as ismaster, mobilephone as mobile, email, starttime, endtime, interviewerid,interviewerid as hrid, ? as coid, ? as coname from RoomInterviewer where roomid = ? and status = ?';
        $interviewers = Db::select($sql, [$subInfo['crmsubid'], $subInfo['subname'], $roomId, '01']);
        $sql = 'select cname as jobseekername, starttime, endtime, autorecordvideo, mobilephone as mobile, email, resumeid as aid, resumeid as cvlogid, rj.caseid as jobid, c.casename as jobname, ? as coid, ? as coname from RoomJobseeker as rj left join SCase as c on rj.caseid = c.caseid where roomid = ? and rj.status = ?';
        $jobseekers = Db::select($sql, [$subInfo['crmsubid'], $subInfo['subname'], $roomId, '01']);
        $roomInfo['interviewdata'] = array_merge($interviewers, $jobseekers);
        $videoService = new VSApi();
        $result = $videoService->buildInterview($roomInfo);
        if ($result['result'] != 1) {
            return [
                'status' => false,
                'message' => '审核失败,' . $result['message']
            ];
        }
        $vs_data = $result['data'] ?? [];
        return Db::connect('jyrpo_pdo')->transaction(function () use ($roomId, $user, $vs_data) {
            $sql = 'update Room set updator = ?, updatedate = NOW(), checkuid = ?, checkdate = NOW(), status = ?, roomid_out = ?, roomnum_out = ? where roomid = ?';
            $var = [
                $user['userid'],
                $user['userid'],
                '02',
                $vs_data['roomid'],
                $vs_data['roomnum'],
                $roomId,
            ];
            Db::connect('jyrpo_pdo')->update($sql, $var);
            foreach ($vs_data['interviewers'] as $interviewer) {
                $sql = 'update RoomInterviewer set updator = ?, checkuid = ?, status = ?, checkdate = NOW(), updatedate = NOW(), interviewerid_out = ?, authorizecode_out = ? where roomid = ? and mobilephone = ? and status = ?';
                $var = [
                    $user['userid'],
                    $user['userid'],
                    '03',
                    $interviewer['interviewerid'],
                    $interviewer['authorizecode'],
                    $roomId,
                    $interviewer['mobile'],
                    '01'
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);
            }

            foreach ($vs_data['jobseekers'] as $jobseeker) {
                $sql = 'update RoomJobseeker set status = ?, jobseekerid_out = ?, authorizecode_out = ?, updator = ?, checkuid = ?, updatedate = NOW(), checkdate = NOW() where roomid = ? and mobilephone = ? and status = ?';
                $var = [
                    '03',
                    $jobseeker['jobseekerid'],
                    $jobseeker['authorizecode'],
                    $user['userid'],
                    $user['userid'],
                    $roomId,
                    $jobseeker['mobile'],
                    '01'
                ];
                Db::connect('jyrpo_pdo')->update($sql, $var);
            }
            return [
                'status' => true,
                'message' => '审核成功'
            ];
        });
    }

    /**
     * Notes:团队房间列表统计
     * @param $params
     * @return mixed
     */
    public function teamRoomListCount($params) {
        $where = $var = [];
        $sql = 'select count(1) as count from Room as r left join Project as p on r.projectid = p.projectid ';
        if ($params['status'] != '00') {
            $where[] = ' r.status = ?';
            $var = [$params['status']];
        }

        if (isset($params['roomname']) && $params['roomname']) {
            $where[] = ' roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $where[] = ' starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        if (isset($params['teamcode']) && $params['teamcode']) {
            $where[] = ' teamcode like ?';
            $var[] = substr($params['teamcode'], 0, 5) . '%';
        }
        if (isset($params['projectname']) && $params['projectname']) {
            $where[] = ' projectname like ?';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['crmprojectnum']) && $params['crmprojectnum']) {
            $where[] = ' crmprojectnum = ?';
            $var[] = $params['crmprojectnum'];
        }
        if ($where) {
            $sql .= ' where ' . implode($where, ' and ');
        }
        return Db::selectOne($sql, $var)['count'];
    }

    /**
     * Notes:一级团队房间列表
     * @param $params
     * @param $rowFrom
     * @param $rowTo
     * @return array
     */
    public function teamRoomList($params, $rowFrom, $rowTo) {
        $where = $var = [];
        $sql = 'select r.roomid, r.subid, p.crmprojectnum, r.projectid, p.projectname, roomname, starttime, endtime, r.creator, linktype, checkuid,r.status, r.createdate from Room as r left join Project as p on r.projectid = p.projectid';
        if ($params['status'] != '00') {
            $where[] = ' r.status = ?';
            $var = [$params['status']];
        }
        if (isset($params['roomname']) && $params['roomname']) {
            $where[] = ' r.roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $where[] = ' starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        if (isset($params['teamcode']) && $params['teamcode']) {
            $where[] = ' r.teamcode like ?';
            $var[] = substr($params['teamcode'], 0, 5) . '%';
        }
        if (isset($params['projectname']) && $params['projectname']) {
            $where[] = ' projectname like ?';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['crmprojectnum']) && $params['crmprojectnum']) {
            $where[] = ' crmprojectnum = ?';
            $var[] = $params['crmprojectnum'];
        }
        if ($where) {
            $sql .= ' where ' . implode($where, ' and ');
        }
        $sql .= ' order by r.roomid desc';
        $roomList = Db::select($sql, $var, $rowFrom, $rowTo);
        $creators = array_column($roomList, 'creator');
        $checkuid = array_diff(array_column($roomList, 'checkuid'), [null]);
        $userIds = array_merge($creators, $checkuid);
        $userList = $this->userList($userIds);
        foreach ($userList as $user) {
            $userMap[$user['userid']] = $user['cname'];
        }

        foreach ($roomList as &$item) {
            $item['creatorname'] = $userMap[$item['creator']] ?? '';
            $item['checkname'] = $userMap[$item['checkuid']] ?? '';
            $item['trans_status'] = $this->roomStatusMap[$item['status']];
        }
        return $roomList;
    }

    /**
     * Notes:我的可用房间列表统计
     * @param $params
     * @return mixed
     */
    public function myUsableRoomCount($params) {
        $userId = UserAuth::getLoginId();
        $sql = 'select count(1) as count from Room where projectid = ? ';
        $sql .= ' and ( (status = ? and endtime > NOW()) or (status = ? and starttime > NOW()) )';
        $var = [$params['projectid'], '02', '01'];
        if (isset($params['roomname']) && $params['roomname']) {
            $sql .= ' and roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $sql .= ' and starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        return Db::selectOne($sql, $var)['count'];
    }

    /**
     * Notes:我的可用房间列表
     * @param $params
     * @param $rowFrom
     * @param $rowTo
     * @return array
     */
    public function myUsableRoomList($params, $rowFrom, $rowTo) {
        $sql = 'select roomid,roomname,starttime,endtime,r.creator,u.cname,r.createdate from Room as r left join User as u on r.creator = u.userid where projectid = ? ';
        $sql .= ' and ( (r.status = ? and endtime > NOW()) or (r.status = ? and starttime > NOW()) )';
        $var = [$params['projectid'], '02', '01'];
        if (isset($params['roomname']) && $params['roomname']) {
            $sql .= ' and roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $sql .= ' and starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        $sql .= ' order by starttime asc,roomid desc';
        $roomList = Db::select($sql, $var, $rowFrom, $rowTo);
        $jobseekerCountMap = [];
        if ($roomList) {
            $roomIds = array_column($roomList, 'roomid');
            $sql = "SELECT roomid,count(1) as totalcount,SUM(if(ISNULL(starttime_video) || starttime_video='',1,0)) as count FROM RoomJobseeker  where roomid in ( " . mergeArrSql($roomIds) . " ) and status != ? group by roomid";
            $jobseekers = Db::select($sql, array_merge($roomIds, ['06']));
            foreach ($jobseekers as $item) {
                $jobseekerCountMap[$item['roomid']] = $item;
            }
        }
        foreach ($roomList as &$room) {
            $room['jobseekercount'] = $jobseekerCountMap[$room['roomid']]['count'] ?? 0;
            $room['jobseekertotalcount'] = $jobseekerCountMap[$room['roomid']]['totalcount'] ?? 0;
        }

        return $roomList;
    }

    /**
     * Notes:我的房间列表统计
     * @param $params
     * @return mixed
     */
    public function myRoomListCount($params) {
        $user = UserAuth::getLoginInfo();
        $sql = 'select count(1) as count from Room as r left join Project as p on r.projectid = p.projectid ';
        $sql .= ' WHERE 1=1 ';
        $var = [];
        if ($user['issuper'] != 1) {
            $sql .= ' and p.projectid in (select projectid from CaseOperator where oprid=? and status=? ';
            $sql .= ' union select projectid from ProjectOperator where oprid=? and status=?)';
            $var = [$user['userid'], '01', $user['userid'], '01'];
        }

        if (isset($params['roomname']) && $params['roomname']) {
            $sql .= ' and roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['projectname']) && $params['projectname']) {
            $sql .= ' and projectname like ?';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['projectid']) && $params['projectid']) {
            $sql .= ' and r.projectid = ?';
            $var[] = $params['projectid'];
        }
        if (isset($params['crmprojectnum']) && $params['crmprojectnum']) {
            $sql .= ' and crmprojectnum = ?';
            $var[] = $params['crmprojectnum'];
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $sql .= ' and starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        return Db::selectOne($sql, $var)['count'];
    }

    /**
     * Notes:我的房间列表
     * @param $params
     * @param $rowFrom
     * @param $rowTo
     * @return array
     */
    public function myRoomList($params, $rowFrom, $rowTo) {
        $user = UserAuth::getLoginInfo();
        $sql = 'select r.roomid, r.subid, r.projectid,p.crmprojectnum,p.projectname, roomname,';
        $sql .= ' starttime, endtime, r.creator, linktype, r.status, r.createdate,u.cname as creatorname';
        $sql .= ' from Room as r left join Project as p on r.projectid = p.projectid ';
        $sql .= ' left join User as u on r.creator = u.userid ';

        $sql .= ' WHERE 1=1 ';
        $var = [];
        if ($user['issuper'] != 1) {
            $sql .= ' and p.projectid in (select projectid from CaseOperator where oprid=? and status=? ';
            $sql .= ' union select projectid from ProjectOperator where oprid=? and status=?)';
            $var = [$user['userid'], '01', $user['userid'], '01'];
        }
        if (isset($params['roomname']) && $params['roomname']) {
            $sql .= ' and roomname like ?';
            $var[] = '%' . $params['roomname'] . '%';
        }
        if (isset($params['projectname']) && $params['projectname']) {
            $sql .= ' and projectname like ?';
            $var[] = '%' . $params['projectname'] . '%';
        }
        if (isset($params['crmprojectnum']) && $params['crmprojectnum']) {
            $sql .= ' and crmprojectnum = ?';
            $var[] = $params['crmprojectnum'];
        }
        if (isset($params['startdate']) && $params['startdate']) {
            $sql .= ' and starttime > ? and starttime < ?';
            $var[] = $params['startdate'] . ' 00:00:00';
            $var[] = $params['startdate'] . ' 23:59:59';
        }
        $sql .= ' order by r.roomid desc';
        $result = Db::select($sql, $var, $rowFrom, $rowTo);
        foreach ($result as &$item) {
            $item['trans_status'] = $this->roomStatusMap[$item['status']];
        }
        return $result;
    }

    /**
     * Notes:审核房间详细信息
     * @param $roomId
     * @return array
     * @throws \Exception
     */
    public function teamRoomDetail($roomId) {
        $user = UserAuth::getLoginInfo();
        $sql = 'select r.roomid,r.roomnum_out,r.subid,r.roomname,r.createdate,s.subname,r.projectid,p.projectname,p.crmprojectnum,starttime,endtime,r.status,r.creator,r.checkuid,r.checkdate,linktype,r.reason,r.istemp from Room as r left join CustomerSub as s on r.subid = s.subid left join Project as p on r.projectid = p.projectid where roomid = ?';
        $var = [$roomId];
        if ($user['issuper'] != 1) {
            $sql .= ' and teamcode like ? ';
            $var[] = substr($user['teamcode'], 0, 5) . '%';
        }
        $room = Db::selectOne($sql, $var);
        if (!$room) {
            return [
                'roominfo' => [],
                'interviewers' => [],
                'jobseekers' => [],
            ];
        }
        return $this->roomDetail($room);
    }

    /**
     * Notes:我的房间详细信息
     * @param $roomId
     * @return array
     * @throws \Exception
     */
    public function myRoomDetail($roomId) {
        $userId = UserAuth::getLoginId();
        $sql = 'select r.roomid,r.roomnum_out,r.subid,r.roomname,r.createdate,s.subname,r.projectid,p.projectname,p.crmprojectnum,starttime,endtime,r.status,r.creator,r.checkuid,r.checkdate,linktype,r.reason,r.istemp  from Room as r left join CustomerSub as s on r.subid = s.subid left join Project as p on r.projectid = p.projectid where roomid = ?';
        $room = Db::selectOne($sql, [$roomId]);
        if (!$room) {
            return [
                'roominfo' => [],
                'interviewers' => [],
                'jobseekers' => [],
            ];
        }

        $hasRight = $this->chkInterviewerRight($room['projectid']);
        if (!$hasRight) {
            return [
                'roominfo' => [],
                'interviewers' => [],
                'jobseekers' => [],
            ];
        }

        return $this->roomDetail($room);
    }

    /**
     * Notes:房间详情
     * @param $room
     * @return array
     * @throws \Exception
     */
    protected function roomDetail($room) {
        $userList = $this->userList([$room['creator'], $room['checkuid']]);
        $cnameMap = [];
        foreach ($userList as $item) {
            $cnameMap [$item['userid']] = $item['cname'];
        }
        $room['creatorname'] = $cnameMap[$room['creator']] ?? '';
        $room['checkname'] = $cnameMap[$room['checkuid']] ?? '';
        $room['trans_status'] = $this->roomStatusMap[$room['status']];
        $room['interview_url'] = config('extraapi.vsapi.interview_url') ?? '';
        $sql = 'select roominterviewerid,interviewerid, cname, mobilephone, email, starttime, endtime, authorizecode_out,interviewerid_out, status, checkdate, starttime_line from RoomInterviewer where roomid = ? and status != ? order by status,master desc,roominterviewerid';
        $interviewers = Db::select($sql, [$room['roomid'], '06']);
        foreach ($interviewers as &$interviewer) {
            $interviewer['trans_status'] = $this->StatusMap[$interviewer['status']];
        }
        $sql = 'select jobseekerid, rj.caseid,c.casename, resumeid, cname, mobilephone, email ,starttime, endtime,jobseekerid_out,authorizecode_out, checkuid, checkdate, rj.status, autorecordvideo,starttime_line, starttime_video, endtime_video, time_video, summary, comment, videourl,rj.createdate  from RoomJobseeker as rj left join SCase as c on rj.caseid = c.caseid where roomid = ? and rj.status != ? order by jobseekerid';
        $jobseekers = Db::select($sql, [$room['roomid'], '06']);

        $sql = 'select DISTINCT jobseekerid from RoomJobseekerVideo where roomid=?';
        $jobseekersVideo = Db::select($sql, [$room['roomid']]);

        $hasVideo = [];
        if (!empty($jobseekersVideo)) {
            foreach ($jobseekersVideo as $itemVideo) {
                $hasVideo[$itemVideo['jobseekerid']] = '1';
            }
        }

        foreach ($jobseekers as &$jobseeker) {
            $jobseeker['trans_status'] = $this->StatusMap[$jobseeker['status']];
            $jobseeker['token'] = UrlCrypt::generateToken([$jobseeker['resumeid']]);
            $jobseeker['hasvideo'] = $hasVideo[$jobseeker['jobseekerid']] ?? '0';
        }
        return [
            'roominfo' => $room,
            'interviewers' => $interviewers,
            'jobseekers' => $jobseekers,
        ];
    }


    /**
     * Notes:添加面试官
     * @param $roomId
     * @param $interviewIds
     * @return array
     */
    public function addInterviewer($roomId, $interviewIds) {
        $user = UserAuth::getLoginInfo();
        if (!is_array($interviewIds) || !$interviewIds) {
            return [
                'status' => true,
                'message' => '添加失败',
            ];
        }
        $sql = 'select subid,projectid,roomid_out,status,starttime,endtime from Room where roomid = ?';
        $room = Db::selectOne($sql, [$roomId]);
        if (!$room || $room['status'] == '03' || $room['status'] == '06') {
            return [
                'status' => false,
                'message' => '非有效房间',
            ];
        }

        $hasRight = $this->chkInterviewerRight($room['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限'
            ];
        }

        $sql = 'select crmsubid, subname from CustomerSub where subid = ?';
        $subInfo = Db::selectOne($sql, [$room['subid']]);
        if (!$subInfo) {
            return [
                'status' => false,
                'message' => '非有效房间',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        if ($room['endtime'] < date('Y-m-d H:i:s')) {
            return [
                'status' => false,
                'message' => '面试已结束，无法添加面试官',
            ];
        }
        $sql = 'select interviewerid,mobilephone from RoomInterviewer where roomid = ? and status in(?,?)';
        $existInterview = Db::select($sql, [$roomId, '01', '03']);
        $existInterviewMobiles = array_column($existInterview, 'mobilephone');
        if (count($existInterviewMobiles) + count($interviewIds) > 9) {
            return [
                'status' => false,
                'message' => '最多添加9个面试官',
            ];
        }
        $sql = 'select id as interviewerid, cname, mobilephone, email  from CustomerAccount where id in ( ' . mergeArrSql($interviewIds) . ' ) and status != ? and isinterviewer = ? ';
        $interviewerInfo = Db::select($sql, array_merge($interviewIds, ['06',1]));
        foreach ($interviewerInfo as $key => &$item) {
            if (in_array($item['mobilephone'], $existInterviewMobiles)) {
                unset($interviewerInfo[$key]);
                continue;
            }
            $item['hrid'] = $item['interviewerid'];
            $item['interviewername'] = $item['cname'];
            $item['mobile'] = $item['mobilephone'];
            $item['starttime'] = $room['starttime'];
            $item['endtime'] = $room['endtime'];
            $item['coid'] = $subInfo['crmsubid'];
            $item['coname'] = $subInfo['subname'];
            $existInterviewMobiles[] = $item['mobilephone'];
        }
        if (!$interviewerInfo) {
            return [
                'status' => false,
                'message' => '重复面试官',
            ];
        }

        $interviewerInfo = array_values($interviewerInfo);
        $vs_data = [];
        //审核通过的房间主动发送请求
        if ($room['status'] == '02') {
            $vidoeService = new VSApi();
            $result = $vidoeService->batchAddInterviewer($room['roomid_out'], $interviewerInfo);
            if ($result['result'] == 1) {
                foreach ($result['data'] as $value) {
                    $vs_data [$value['mobile']] = $value;
                }
            }
        }
        $sql = 'insert into RoomInterviewer (roomid, subid, projectid, interviewerid, cname, mobilephone, email, starttime, endtime, interviewerid_out, authorizecode_out,master, status, creator, updator, createdate, updatedate) values';
        $values = $var = $tmpVar = [];
        foreach ($interviewerInfo as $interviewer) {
            $values[] = '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
            $tmpVar = [
                $roomId,
                $room['subid'],
                $room['projectid'],
                $interviewer['interviewerid'],
                $interviewer['cname'],
                $interviewer['mobilephone'],
                $interviewer['email'],
                $room['starttime'],
                $room['endtime'],
                $vs_data[$interviewer['mobilephone']]['interviewerid'] ?? '',
                $vs_data[$interviewer['mobilephone']]['authorizecode'] ?? '',
                1,
                isset($vs_data[$interviewer['mobilephone']]['authorizecode']) ? '03' : '01',
                $user['userid'],
                $user['userid'],
            ];
            $var = array_merge($var, $tmpVar);
        }
        if ($values) {
            $sql .= implode($values, ',');
            Db::insert($sql, $var);
        }
        return [
            'status' => true,
            'message' => '添加成功',
        ];

    }

    /**
     * Notes:添加求职者
     * @param $roomId
     * @param $jobseekerInfo
     * @return array|mixed
     * @throws \Throwable
     */
    public function addJobseeker($roomId, $jobseekerInfo) {
        $userId = UserAuth::getLoginId();
        if (count($jobseekerInfo) > 100) {
            return [
                'status' => false,
                'message' => '求职者不能大于100人',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        $sql = 'select subid,projectid,starttime,endtime,status,roomid_out from Room where roomid = ?';
        $room = Db::selectOne($sql, [$roomId]);
        if (!$room || $room['status'] == '03' || $room['status'] == '06') {
            return [
                'status' => false,
                'message' => '非有效房间',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }

        $hasRight = $this->chkInterviewerRight($room['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }

        $endtime = date('Y-m-d', strtotime($room['starttime'])) . ' 23:59:59';
        $sql = 'select crmsubid, subname from CustomerSub where subid = ?';
        $subInfo = Db::selectOne($sql, [$room['subid']]);
        if (!$subInfo) {
            return [
                'status' => false,
                'message' => '非有效房间',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        if ($room['endtime'] < date('Y-m-d H:i:s')) {
            return [
                'status' => false,
                'message' => '面试已结束，无法添加求职者',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        $sql = 'select mobilephone from RoomJobseeker where roomid = ? and status in(?,?)';
        $existMobile = Db::select($sql, [$roomId, '01', '03']);

        if ($existMobile) {
            $existMobile = array_column($existMobile, 'mobilephone');
        }
        if (count($existMobile) + count($jobseekerInfo) > 100) {
            return [
                'status' => false,
                'message' => '房间内求职者不能大于100人',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        $sql = 'select interviewerid, interviewerid_out from RoomInterviewer where roomid = ? and status !=?';
        $interviewer = Db::selectOne($sql, [$roomId, '06']);
        if (!$interviewer) {
            return [
                'status' => false,
                'message' => '此房间无面试官',
                'errorresumeids' => [],
                'existresumeids' => [],
            ];
        }
        $allCaseIds = array_column($jobseekerInfo, 'caseid');
        $casenameMap = [];
        if ($allCaseIds) {
            $sql = 'select caseid, casename from SCase where caseid in ( ' . mergeArrSql($allCaseIds) . ' )';
            $allCase = Db::select($sql, $allCaseIds);
            foreach ($allCase as $item) {
                $casenameMap[$item['caseid']] = $item['casename'];
            }
        }
        $vs_dataMap = $errorResumeIds = $existResumeIds = [];
        //字段名调整、参数拼接
        foreach ($jobseekerInfo as $key => &$jobseeker) {
            //已存在和无效的数据剔除
            if (!$jobseeker['cname'] || !$jobseeker['mobilephone']) {
                unset($jobseekerInfo[$key]);
                $errorResumeIds [] = $jobseeker['cname'] ?: $jobseeker['resumeid'];
                continue;
            }

            if ((!Validation::validateEmail('', $jobseeker['email']) || strlen($jobseeker['email']) >= 80)) {
                $jobseeker['email'] = '';
            }

            if (preg_match('/(^(((\+)?\d{1,4}-)\d{5,15}$)|(^1\d{10}$))/', $jobseeker['mobilephone']) == 0) {
                $errorResumeIds [] = $jobseeker['cname'] ?: $jobseeker['resumeid'];
                unset($jobseekerInfo[$key]);
                continue;
            }

            $jobseeker['mobilephone'] = ltrim($jobseeker['mobilephone'], '+');
            $phoneArr = explode("-", $jobseeker['mobilephone']);
            if (count($phoneArr) == 2) {
                if ($phoneArr[0] == 86) {
                    $jobseeker['mobilephone'] = $phoneArr[1];
                } else {
                    $jobseeker['mobilephone'] = ltrim($jobseeker['mobilephone'], 0);
                }
            }

            if (in_array($jobseeker['mobilephone'], $existMobile)) {
                $existResumeIds [] = $jobseeker['cname'] ?: $jobseeker['resumeid'];
                unset($jobseekerInfo[$key]);
                continue;
            }
            if (date('Y-m-d', strtotime($jobseeker['starttime'])) != date('Y-m-d', strtotime($room['starttime']))) {
                unset($jobseekerInfo[$key]);
                continue;
            }
            $jobseeker['endtime'] = $endtime;
            $jobseeker['jobseekername'] = $jobseeker['cname'];
            $jobseeker['mobile'] = $jobseeker['mobilephone'];
            $jobseeker['aid'] = $jobseeker['resumeid'];
            $jobseeker['cvlogid'] = $jobseeker['resumeid'];
            $jobseeker['autorecordvideo'] = $this->recordVideoType;
            $jobseeker['coid'] = $subInfo['crmsubid'];
            $jobseeker['coname'] = $subInfo['subname'];
            $jobseeker['jobid'] = $jobseeker['caseid'];
            $jobseeker['jobname'] = $casenameMap[$jobseeker['caseid']] ?? '';
            $existMobile [] = $jobseeker['mobilephone'];
        }
        $message = $errorResumeIds ? '<br>姓名或手机不符合条件：' . implode($errorResumeIds, '，') : '';
        $message .= $existResumeIds ? '<br>已在房间内简历：' . implode($existResumeIds, '，') : '';

        if (!$jobseekerInfo) {
            return [
                'status' => false,
                'message' => '保存失败，无有效的简历数据：' . $message,
                'errorresumeids' => $errorResumeIds,
                'existresumeids' => $existResumeIds,
            ];
        }

        if ($room['status'] == '02') {
            $videoService = new VSApi();
            $result = $videoService->batchAddJobSeeker($room['roomid_out'], array_values($jobseekerInfo));
            if ($result['result'] != 1) {
                return [
                    'status' => false,
                    'message' => '无法添加求职者，' . $result['message'] . $message,
                    'errorresumeids' => $errorResumeIds,
                    'existresumeids' => $existResumeIds,
                ];
            }
            foreach ($result['data'] as $item) {
                $vs_dataMap[$item['mobile']] = [
                    'vs_jobseekerid' => $item['jobseekerid'],
                    'authorizecode' => $item['authorizecode'],
                ];
            }
        }

        return Db::connect('jyrpo_pdo')->transaction(function () use ($roomId, $room, $jobseekerInfo, $userId, $interviewer, $vs_dataMap, $errorResumeIds, $existResumeIds, $message) {
            $sql = 'insert into RoomJobseeker (roomid, subid, projectid, caseid, resumeid, cname, mobilephone, email, keycode_caseresume, starttime, endtime, jobseekerid_out, authorizecode_out, autorecordvideo, creator, updator, status, createdate, updatedate) values ';
            $values = $var = $uuids = [];
            foreach ($jobseekerInfo as $jobseeker) {
                $values [] = '(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())';
                $tmpVar = [
                    $roomId,
                    $room['subid'],
                    $room['projectid'],
                    $jobseeker['caseid'],
                    $jobseeker['resumeid'],
                    $jobseeker['cname'],
                    $jobseeker['mobilephone'],
                    $jobseeker['email'],
                    $jobseeker['keycode'],
                    $jobseeker['starttime'],
                    $jobseeker['endtime'],
                    $vs_dataMap[$jobseeker['mobilephone']]['vs_jobseekerid'] ?? '',
                    $vs_dataMap[$jobseeker['mobilephone']]['authorizecode'] ?? '',
                    $this->recordVideoType,
                    $userId,
                    $userId,
                    isset($vs_dataMap[$jobseeker['mobilephone']]['authorizecode']) ? '03' : '01'
                ];
                $var = array_merge($var, $tmpVar);
                //防止变量超过1000
                if (count($var) > 800) {
                    $sql .= implode($values, ',');
                    Db::connect('jyrpo_pdo')->insert($sql, $var);
                    $var = $values = [];
                    $sql = 'insert into RoomJobseeker (roomid, subid, projectid, caseid, resumeid, cname, mobilephone, email, keycode_caseresume, starttime, endtime, jobseekerid_out, authorizecode_out, autorecordvideo, creator, updator, createdate, updatedate) values ';
                }
            }
            if ($values) {
                $sql .= implode($values, ',');
                Db::connect('jyrpo_pdo')->insert($sql, $var);
            }
            return [
                'status' => empty($message),
                'message' => '添加成功' . $message,
                'errorresumeids' => $errorResumeIds,
                'existresumeids' => $existResumeIds,
            ];
        });
    }

    /**
     * Notes:修改求职者面试安排时间
     * @param $jobseekers
     * @param $roomId
     * @return array
     * @throws \GuzzleHttp\Exception\GuzzleException
     */
    public function updateJobseekerTime($jobseekers, $roomId) {
        $user = UserAuth::getLoginInfo();
        $sql = 'select roomid,projectid,roomid_out,status,starttime from Room where roomid = ? ';
        $room = Db::selectOne($sql, [$roomId]);
        if (!$room || $room['status'] == '03' || $room['status'] == '06') {
            return [
                'status' => false,
                'message' => '无效房间'
            ];
        }
        $jobseekerIds = array_column($jobseekers, 'jobseekerid');
        if (!$jobseekerIds) {
            return [
                'status' => false,
                'message' => '无效数据'
            ];
        }

        $hasRight = $this->chkInterviewerRight($room['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限'
            ];
        }

        $jobseekerMap = $subNameMap = $caseMap = $errorList = $errorMap = [];
        $sql = 'select jobseekerid,roomid,status,caseid,subid,jobseekerid_out,cname,mobilephone,email,resumeid,autorecordvideo,starttime from RoomJobseeker where jobseekerid in ( ' . mergeArrSql($jobseekerIds) . ' ) and status != ? and starttime_video is null';
        $jobseekerInfo = Db::select($sql, array_merge($jobseekerIds, ['06']));
        if (!$jobseekerInfo) {
            return [
                'status' => false,
                'message' => '无效数据'
            ];
        }
        foreach ($jobseekerInfo as $item) {
            $jobseekerMap[$item['jobseekerid']] = $item;
        }
        $allSubIds = array_column($jobseekerInfo, 'subid');
        $sql = 'select subname,subid from CustomerSub where subid in ( ' . mergeArrSql($allSubIds) . ' )';
        $subInfo = Db::select($sql, $allSubIds);
        foreach ($subInfo as $item) {
            $subNameMap[$item['subid']] = $item['subname'];
        }
        $allCaseIds = array_column($jobseekerInfo, 'caseid');
        $sql = 'select caseid,casename from SCase where caseid in ( ' . mergeArrSql($allCaseIds) . ' )';
        $caseInfo = Db::select($sql, $allCaseIds);
        foreach ($caseInfo as $item) {
            $caseMap[$item['caseid']] = $item['casename'];
        }

        $endtime = date('Y-m-d', strtotime($room['starttime'])) . ' 23:59:59';
        foreach ($jobseekers as $jobseeker) {
            $jobseekerDetail = $jobseekerMap[$jobseeker['jobseekerid']] ?? [];
            if ($jobseekerDetail) {
                if ($jobseekerDetail['starttime'] == $jobseeker['starttime']) {
                    continue;
                }
                if (date('Y-m-d H:i:s') > $jobseeker['starttime']) {
                    $errorList [] = [
                        'jobseekername' => $jobseekerDetail['cname'],
                        'error' => '开始时间需大于当前时间',
                    ];
                    $errorMap['开始时间需大于当前时间'][] = $jobseekerDetail['cname'];
                    continue;
                }
                if ($jobseekerDetail['status'] == '03') {
                    $jobseekerDetail['subname'] = $subNameMap[$jobseekerDetail['subid']] ?? '';
                    $jobseekerDetail['casename'] = $subNameMap[$jobseekerDetail['caseid']] ?? '';
                    $jobseekerDetail['roomid_out'] = $room['roomid_out'];
                    $jobseekerDetail['starttime'] = $jobseeker['starttime'];
                    $jobseekerDetail['endtime'] = $endtime;
                    $videoService = new VSApi();
                    $result = $videoService->updateJobseeker($jobseekerDetail);
                    if ($result['result'] != 1) {
                        $errorList [] = [
                            'jobseekername' => $jobseekerDetail['cname'],
                            'error' => $result['message'],
                        ];
                        $errorMap[$result['message']][] = $jobseekerDetail['cname'];
                        continue;
                    }
                }
                $sql = 'update RoomJobseeker set updator = ?, starttime = ?, endtime = ?, updatedate = NOW() where jobseekerid = ?';
                $var = [
                    $user['userid'],
                    $jobseeker['starttime'],
                    $endtime,
                    $jobseeker['jobseekerid']
                ];
                $result = Db::update($sql, $var);
                if ($result != 1) {
                    $errorList [] = [
                        'jobseekername' => $jobseekerDetail['cname'],
                        'error' => '添加失败',
                    ];
                    $errorMap['添加失败'][] = $jobseekerDetail['cname'];
                }
            }
        }
        $message = '';
        foreach ($errorMap as $key => $item) {
            $message .= ';' . $key . ':' . implode($item, ',');
        }
        return [
            'status' => empty($message),
            'message' => empty($message) ? '修改成功' : ('修改失败' . $message),
            'errorlist' => $errorList
        ];
    }

    /**
     * Notes:删除面试官
     * @param $roomId
     * @param $roomInterviewerId
     * @return array
     */
    public function delInterview($roomId, $roomInterviewerId) {
        $userId = UserAuth::getLoginId();
        $sql = 'select projectid,status,interviewerid_out,starttime from RoomInterviewer where roomid = ? and roominterviewerid = ?';
        $interviewer = Db::selectOne($sql, [$roomId, $roomInterviewerId]);
        if (!$interviewer || $interviewer['status'] == '06') {
            return [
                'status' => false,
                'message' => '删除失败'
            ];
        }
        if ($interviewer['starttime'] < date('Y-m-d H:i:s', strtotime('+45 minutes'))) {
            return [
                'status' => false,
                'message' => '面试即将开始或已结束，无法删除'
            ];
        }
        $sql = 'select status from Room where roomid = ?';
        $roomStatus = Db::selectOne($sql, [$roomId]);
        if (!$roomStatus) {
            return [
                'status' => false,
                'message' => '删除失败'
            ];
        }

        $hasRight = $this->chkInterviewerRight($interviewer['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限'
            ];
        }

        if ($roomStatus['status'] == '01') {
            $status = '01';
        } elseif ($roomStatus['status'] == '02') {
            $status = '03';
        } else {
            return [
                'status' => false,
                'message' => '删除失败'
            ];
        }
        $sql = 'select * from RoomInterviewer where roomid = ? and roominterviewerid != ? and creator = ? and status = ?';
        $lastInterviewer = Db::select($sql, [$roomId, $roomInterviewerId, $userId, $status]);
        if (!$lastInterviewer) {
            return [
                'status' => false,
                'message' => '最后一个面试官无法删除'
            ];
        }
        //待审核和不通过审核的只需添加数据库
        if ($interviewer['status'] == '01' || $interviewer['status'] == '05') {
            $sql = 'update RoomInterviewer set updator = ?, updatedate = NOW(), status = ? where roomid = ? and roominterviewerid = ?';
            $var = [
                $userId,
                '06',
                $roomId,
                $roomInterviewerId
            ];
            $result = Db::update($sql, $var);
            return [
                'status' => $result == 1,
                'message' => $result == 1 ? '删除成功' : '删除失败'
            ];
            //通过审核的需调用取消面试官接口
        } elseif ($interviewer['status'] == '03') {
            $sql = 'select roomid_out from Room where roomid = ?';
            $vs_roomid = Db::selectOne($sql, [$roomId]);
            if (!$vs_roomid) {
                return [
                    'status' => false,
                    'message' => '删除失败，请稍后再试'
                ];
            }

            $videoService = new VSApi();
            $result = $videoService->delInterviewer($vs_roomid['roomid_out'], $interviewer['interviewerid_out']);
            if ($result['result'] != 1) {
                return [
                    'status' => false,
                    'message' => '面试官不可删除，' . $result['message']
                ];
            }
            $sql = 'update RoomInterviewer set updator = ? ,updatedate = NOW(), status = ? where roomid = ? and roominterviewerid = ?';
            $var = [
                $userId,
                '06',
                $roomId,
                $roomInterviewerId
            ];
            $result = Db::update($sql, $var);
            return [
                'status' => $result == 1,
                'message' => $result == 1 ? '删除成功' : '删除失败'
            ];
        }
    }

    /**
     * Notes:删除求职者
     * @param $roomId
     * @param $jobseekerId
     * @return array
     */
    public function delJobseeker($roomId, $jobseekerId) {
        $userId = UserAuth::getLoginId();
        $sql = 'select projectid,status,jobseekerid_out,starttime from RoomJobseeker where roomid = ? and jobseekerid = ?';
        $jobseeker = Db::selectOne($sql, [$roomId, $jobseekerId]);
        if (!$jobseeker || $jobseeker['status'] == '06') {
            return [
                'status' => false,
                'message' => '删除失败'
            ];
        }

        if ($jobseeker['starttime'] < date('Y-m-d H:i:s', strtotime('+45 minutes'))) {
            return [
                'status' => false,
                'message' => '面试即将开始或已结束，无法删除求职者'
            ];
        }

        $hasRight = $this->chkInterviewerRight($jobseeker['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限'
            ];
        }

        if ($jobseeker['status'] == '01' || $jobseeker['status'] == '05') {
            $sql = 'update RoomJobseeker set updator = ?, updatedate = NOW(), status = ? where roomid = ? and jobseekerid = ?';
            $var = [
                $userId,
                '06',
                $roomId,
                $jobseekerId
            ];
            $result = Db::update($sql, $var);
            return [
                'status' => $result == 1,
                'message' => $result == 1 ? '删除成功' : '删除失败'
            ];
        } elseif ($jobseeker['status'] == '03') {
            $sql = 'select roomid_out from Room where roomid = ?';
            $vs_roomid = Db::selectOne($sql, [$roomId]);
            if (!$vs_roomid) {
                return [
                    'status' => false,
                    'message' => '删除失败，请稍后再试'
                ];
            }

            $videoService = new VSApi();
            $result = $videoService->delJobSeeker($vs_roomid['roomid_out'], $jobseeker['jobseekerid_out']);
            if ($result['result'] != 1) {
                return [
                    'status' => false,
                    'message' => '求职者不可删除，' . $result['message']
                ];
            }
            $sql = 'update RoomJobseeker set updator = ? ,updatedate = NOW(), status = ? where roomid = ? and jobseekerid = ?';
            $var = [
                $userId,
                '06',
                $roomId,
                $jobseekerId
            ];
            $result = Db::update($sql, $var);
            return [
                'status' => $result == 1,
                'message' => $result == 1 ? '删除成功' : '删除失败'
            ];

        }
    }

    /**
     * Notes:简历面试记录
     * @param $resumeIds
     * @return array
     */
    public function resumeInterviewRecords($resumeIds) {
        $records = $recordsMap = [];
        if (!$resumeIds) {
            return $records;
        }
        $sql = 'select rj.roomid,rj.createdate,rj.resumeid,rj.starttime,r.roomname,caseid,cname,mobilephone,email,rj.starttime,rj.endtime,summary,comment,videourl from RoomJobseeker as rj left join Room as r on r.roomid = rj.roomid where rj.createdate > ? and rj.resumeid in ( ' . mergeArrSql($resumeIds) . ' )';
        $result = Db::select($sql, array_merge([date('Y-m-d', strtotime('-6 month'))], $resumeIds));
        foreach ($result as $item) {
            $recordsMap[$item['resumeid']][] = $item;
        }
        foreach ($resumeIds as $resumeId) {
            $records [] = [
                'resumeid' => $resumeId,
                'records' => $recordsMap[$resumeId] ?? [],
            ];
        }
        return $records;
    }

    /**
     * Notes:取消面试
     * @param $roomId
     * @return array
     */
    public function cancelRoom($roomId) {
        $userId = UserAuth::getLoginId();
        $sql = 'select status, roomid_out, starttime from Room where roomid = ? and creator = ?';
        $room = Db::selectOne($sql, [$roomId, $userId]);
        if (!$room) {
            return [
                'status' => false,
                'message' => '只能取消自己创建的房间'
            ];
        }
        if ($room['status'] == '06') {
            return [
                'status' => false,
                'message' => '取消失败，请稍后再试'
            ];
        }
        if ($room['status'] == '02' && $room['starttime'] < date('Y-m-d H:i:s', strtotime('+45 minutes'))) {
            return [
                'status' => false,
                'message' => '面试即将开始或已结束，无法取消'
            ];
        }

        $videoService = new VSApi();
        if ($room['status'] == '02') {
            $result = $videoService->cancelRoom($room['roomid_out']);
            if ($result['result'] != 1) {
                return [
                    'status' => false,
                    'message' => '面试无法取消，' . $result['message']
                ];
            }
        }
        Db::connect('jyrpo_pdo')->transaction(function () use ($roomId, $userId) {
            $sql = 'update Room set status = ?, updator = ?, updatedate = NOW() where roomid = ?';
            $var = [
                '06',
                $userId,
                $roomId
            ];
            Db::connect('jyrpo_pdo')->update($sql, $var);
            $sql = 'update RoomInterviewer set status = ? , updator = ?, updatedate = NOW() where roomid = ?';
            Db::connect('jyrpo_pdo')->update($sql, $var);
            $sql = 'update RoomJobseeker set status = ? , updator = ?, updatedate = NOW() where roomid = ?';
            Db::connect('jyrpo_pdo')->update($sql, $var);
        });
        return [
            'status' => true,
            'message' => '取消成功'
        ];
    }


    /**
     * Notes:查询用户信息
     * @param $userIds
     * @return array
     */
    protected function userList($userIds) {
        $userIds = array_unique($userIds);
        $count = count($userIds);
        $userList = [];
        for ($i = 0; $i <= $count; $i += 100) {
            $tmpUserIds = array_slice($userIds, $i, 100);
            if (!$tmpUserIds) {
                break;
            }
            $sql = 'select userid, cname from User where userid in ( ' . mergeArrSql($tmpUserIds) . ' )';
            $tmpUserList = Db::select($sql, $tmpUserIds);
            $userList = array_merge($userList, $tmpUserList);
        }
        return $userList;
    }

    //我的所有求职者
    public function myJobseekers($vs_jobseekerIds) {
        $userId = UserAuth::getLoginId();
        $sql = 'select projectid,jobseekerid_out from RoomJobseeker where status = ? and endtime> NOW() and jobseekerid_out in (' . mergeArrSql($vs_jobseekerIds) . ')';
        return Db::select($sql, array_merge(['03'], $vs_jobseekerIds));
    }

    //我的所有面试官
    public function myInterviewer($interviewerid_out) {
        $userId = UserAuth::getLoginId();
        $sql = 'select projectid,interviewerid_out from RoomInterviewer where status = ? and endtime> NOW() and interviewerid_out in (' . mergeArrSql($interviewerid_out) . ')';
        return Db::select($sql, array_merge(['03'], $interviewerid_out));
    }

    //面试结果
    public function interviewResult($roomId, $jobseekerId) {
        $sql = 'select projectid,status,roomid_out,starttime from Room where roomid = ?';
        $room = Db::selectOne($sql, [$roomId]);
        if (!$room) {
            return [
                'status' => false,
                'message' => '房间不存在',
                'interviewresult' => []
            ];
        }

        $hasRight = $this->chkInterviewerRight($room['projectid']);
        if (!$hasRight) {
            return [
                'status' => false,
                'message' => '无权限',
                'interviewresult' => []
            ];
        }

        $sql = 'select rjr.roomid, rjr.roominterviewerid,ri.projectid, ri.cname, summary, score, comment, rjr.createdate from RoomJobseekerResult as rjr left join RoomInterviewer as ri on rjr.roominterviewerid = ri.roominterviewerid where rjr.roomid = ? and rjr.jobseekerid = ?';
        $result = Db::select($sql, [$roomId, $jobseekerId]);
        return [
            'status' => true,
            'message' => '获取成功',
            'interviewresult' => $result
        ];
    }


    /**判断是否有项视频面试权限
     * @param $projectId
     * @return bool
     */
    public function chkInterviewerRight($projectId) {
        $user = UserAuth::getLoginInfo();
        //超管
        if ($user['issuper'] == 1) {
            return true;
        }
        $user = UserAuth::getLoginInfo();
        $sql = 'SELECT subid FROM CaseOperator WHERE oprid=? AND projectid=? AND status=?';
        $var = [$user['userid'], $projectId, '01'];
        $result = Db::selectOne($sql, $var);
        if (isset($result['subid'])) {
            return true;
        }

        $sql = 'SELECT subid FROM ProjectOperator WHERE oprid=? AND projectid=? AND status=?';
        $var = [$user['userid'], $projectId, '01'];
        $result = Db::selectOne($sql, $var);
        if (isset($result['subid'])) {
            return true;
        }
        return false;
    }

    public function export($roomId) {
        $roomInfo = $this->myRoomDetail($roomId);
        if (!$roomInfo['roominfo']) {
            return response()->json([
                'status' => false,
                'message' => '下载错误',
            ]);
        }

        $jobseekerResultMap = $jobseekerVideoResultMap = $interviewersMap = [];

        //求职者面试结果
        $sql = 'select jobseekerid,roominterviewerid,summary,comment,score,createdate from RoomJobseekerResult where roomid = ? order by jobseekerid,createdate';
        $jobseekerResult = Db::select($sql, [$roomId]);
        foreach ($jobseekerResult as $item) {
            $jobseekerResultMap[$item['jobseekerid']][] = $item;
        }

        //求职者面试录像
        $sql = 'select jobseekerid,mediaurl,starttime,endtime from RoomJobseekerVideo where roomid = ? order by jobseekerid,starttime';
        $jobseekerVideoResult = Db::select($sql, [$roomId]);
        foreach ($jobseekerVideoResult as $item) {
            $jobseekerVideoResultMap[$item['jobseekerid']][] = $item;
        }

        foreach ($roomInfo['interviewers'] as $interviewer) {
            $interviewersMap[$interviewer['roominterviewerid']] = $interviewer['cname'];
        }

        $title = ['房间ID', '房间名称', '房间地址', '项目编号', '项目名称', '面试日期', '面试时间', '创建人', '创建时间', '审核人', '审核时间', '当前状态'];
        $title2 = ['姓名', '手机', '邮箱', '登录凭证'];
        $title3 = ['简历ID', '姓名', '职位名称', '手机', '邮箱', '加入时间', '邀请面试时间', '登录凭证', '开始时间', '结束时间', '时长', '视频录像'];
        $title4 = ['面试官名称', '概括评价', '评语', '分数', '评价时间'];
        $title5 = ['开始时间', '结束时间', '录像链接'];

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle('房间信息');
        $sheet2 = $spreadsheet->createSheet(1)->setTitle('面试官信息');
        $sheet3 = $spreadsheet->createSheet(2)->setTitle('求职者信息');

        //设置表头
        foreach ($title as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        foreach ($title2 as $key => $value) {
            $sheet2->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        foreach ($title3 as $key => $value) {
            $sheet3->setCellValueByColumnAndRow($key + 1, 1, $value);
        }

        // sheet1 房间详情
        $sheet->setCellValueByColumnAndRow(1, 2, $roomInfo['roominfo']['roomid']);
        $sheet->setCellValueByColumnAndRow(2, 2, $roomInfo['roominfo']['roomname']);
        $roomUrl = config('app.host_domain') . '/videoroominfo?roomid=' . $roomInfo['roominfo']['roomid'] . '&type=my';
        $sheet->setCellValueByColumnAndRow(3, 2, $roomUrl);
        $sheet->setCellValueByColumnAndRow(4, 2, $roomInfo['roominfo']['crmprojectnum']);
        $sheet->setCellValueByColumnAndRow(5, 2, $roomInfo['roominfo']['projectname']);
        $interviewDate = date('Y-m-d', strtotime($roomInfo['roominfo']['starttime']));
        $interviewTime = date('H:i:s', strtotime($roomInfo['roominfo']['starttime'])) . '-' . date('H:i:s', strtotime($roomInfo['roominfo']['endtime']));
        $sheet->setCellValueByColumnAndRow(6, 2, $interviewDate);
        $sheet->setCellValueByColumnAndRow(7, 2, $interviewTime);
        $sheet->setCellValueByColumnAndRow(8, 2, $roomInfo['roominfo']['creatorname']);
        $sheet->setCellValueByColumnAndRow(9, 2, $roomInfo['roominfo']['createdate']);
        $sheet->setCellValueByColumnAndRow(10, 2, $roomInfo['roominfo']['checkname']);
        $sheet->setCellValueByColumnAndRow(11, 2, $roomInfo['roominfo']['checkdate']);
        $sheet->setCellValueByColumnAndRow(12, 2, $this->roomStatusMap[$roomInfo['roominfo']['status']]);
        // sheet2 面试官信息
        $rowOfSheet2 = 2;
        foreach ($roomInfo['interviewers'] as $interviewer) {
            $sheet2->setCellValueByColumnAndRow(1, $rowOfSheet2, $interviewer['cname']);
            $sheet2->setCellValueByColumnAndRow(2, $rowOfSheet2, $interviewer['mobilephone']);
            $sheet2->setCellValueByColumnAndRow(3, $rowOfSheet2, $interviewer['email']);
            $sheet2->setCellValueByColumnAndRow(4, $rowOfSheet2, $interviewer['authorizecode_out']);
            $rowOfSheet2++;
        }
        // sheet3 求职者信息
        $rowOfSheet3 = 2; // 从第二行开始
        foreach ($roomInfo['jobseekers'] as $jobseeker) {
            $sheet3->setCellValueByColumnAndRow(1, $rowOfSheet3, $jobseeker['resumeid']);
            $sheet3->setCellValueByColumnAndRow(2, $rowOfSheet3, $jobseeker['cname']);
            $sheet3->setCellValueByColumnAndRow(3, $rowOfSheet3, $jobseeker['casename']);
            $sheet3->setCellValueByColumnAndRow(4, $rowOfSheet3, $jobseeker['mobilephone']);
            $sheet3->setCellValueByColumnAndRow(5, $rowOfSheet3, $jobseeker['email']);
            $sheet3->setCellValueByColumnAndRow(6, $rowOfSheet3, $jobseeker['createdate']);
            $sheet3->setCellValueByColumnAndRow(7, $rowOfSheet3, $jobseeker['starttime']);
            $sheet3->setCellValueByColumnAndRow(8, $rowOfSheet3, $jobseeker['authorizecode_out']);
            $sheet3->setCellValueByColumnAndRow(9, $rowOfSheet3, $jobseeker['starttime_video']);
            $sheet3->setCellValueByColumnAndRow(10, $rowOfSheet3, $jobseeker['endtime_video']);
            $sheet3->setCellValueByColumnAndRow(11, $rowOfSheet3, $jobseeker['time_video'] > 0 ? ceil((int)($jobseeker['time_video']) / 60) . '分钟' : '');
            $sheet3->setCellValueByColumnAndRow(12, $rowOfSheet3, $jobseeker['hasvideo'] == '1' ? '有录像' : '');
            $jobseekerResult = $jobseekerResultMap[$jobseeker['jobseekerid']] ?? [];
            if ($jobseekerResult) {
                $rowOfSheet3++;
                $sheet3->setCellValueByColumnAndRow(2, $rowOfSheet3, '面试结果：');
                foreach ($title4 as $key => $value) {
                    $sheet3->setCellValueByColumnAndRow($key + 3, $rowOfSheet3, $value);
                }
                $rowOfSheet3++;
                foreach ($jobseekerResult as $key => $item) {
                    $sheet3->setCellValueByColumnAndRow(3, $rowOfSheet3, $interviewersMap[$item['roominterviewerid']] ?? '');
                    $sheet3->setCellValueByColumnAndRow(4, $rowOfSheet3, $item['summary']);
                    $sheet3->setCellValueByColumnAndRow(5, $rowOfSheet3, $item['comment']);
                    $sheet3->setCellValueByColumnAndRow(6, $rowOfSheet3, $item['score']);
                    $sheet3->setCellValueByColumnAndRow(7, $rowOfSheet3, $item['createdate']);
                    if (count($jobseekerResult) - 1 > $key) {
                        $rowOfSheet3++;
                    }
                }
            }

            $jobseekerVideoResult = $jobseekerVideoResultMap[$jobseeker['jobseekerid']] ?? [];
            if ($jobseekerVideoResult) {
                $rowOfSheet3++;
                $sheet3->setCellValueByColumnAndRow(2, $rowOfSheet3, '视频录像：');
                foreach ($title5 as $key => $value) {
                    $sheet3->setCellValueByColumnAndRow($key + 3, $rowOfSheet3, $value);
                }
                $rowOfSheet3++;

                foreach ($jobseekerVideoResult as $key => $item) {
                    $sheet3->setCellValueByColumnAndRow(3, $rowOfSheet3, $item['starttime']);
                    $sheet3->setCellValueByColumnAndRow(4, $rowOfSheet3, $item['endtime']);
                    $sheet3->setCellValueByColumnAndRow(5, $rowOfSheet3, $item['mediaurl']);
                    if (count($jobseekerResult) - 1 > $key) {
                        $rowOfSheet3++;
                    }
                }
            }

            $rowOfSheet3++;
        }

        //设定样式
        $sheet->getColumnDimension('A')->setWidth(10);
        $sheet->getColumnDimension('B')->setWidth(30);
        $sheet->getColumnDimension('C')->setWidth(40);
        $sheet->getColumnDimension('D')->setWidth(10);
        $sheet->getColumnDimension('E')->setWidth(50);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(20);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(20);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(20);
        $sheet->getColumnDimension('L')->setWidth(15);

        $sheet2->getColumnDimension('A')->setWidth(15);
        $sheet2->getColumnDimension('B')->setWidth(15);
        $sheet2->getColumnDimension('C')->setWidth(40);
        $sheet2->getColumnDimension('D')->setWidth(20);

        $sheet3->getColumnDimension('A')->setWidth(12);
        $sheet3->getColumnDimension('B')->setWidth(12);
        $sheet3->getColumnDimension('C')->setWidth(30);
        $sheet3->getColumnDimension('D')->setWidth(20);
        $sheet3->getColumnDimension('E')->setWidth(20);
        $sheet3->getColumnDimension('F')->setWidth(20);
        $sheet3->getColumnDimension('G')->setWidth(20);
        $sheet3->getColumnDimension('H')->setWidth(15);
        $sheet3->getColumnDimension('I')->setWidth(20);
        $sheet3->getColumnDimension('J')->setWidth(20);
        $sheet3->getColumnDimension('K')->setWidth(10);
        $sheet3->getColumnDimension('L')->setWidth(15);
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        },
            '视频面试房间详情.xlsx',
            ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8']);
    }

    /**
     * Notes:检查面试官时间
     * @param $starttime
     * @param $endtime
     * @param $interviewerIds
     * @return array
     */
    public function checkInterviewers($starttime, $endtime, $interviewerIds) {
        $sql = 'select cname,mobilephone,starttime,endtime from RoomInterviewer where interviewerid in(' . mergeArrSql($interviewerIds) . ' ) and starttime>=? and endtime<=? and status in(?, ?) and ( (starttime<=? and endtime>=?) or (starttime<=? and endtime>=?) or (starttime>=? and endtime<=?) ) order by interviewerid,starttime;';
        $interviewDate = date('Y-m-d', strtotime($starttime));
        $var = [
            $interviewDate . ' 00:00:00',
            $interviewDate . ' 23:59:59',
            '01',
            '03',
            $starttime,
            $starttime,
            $endtime,
            $endtime,
            $starttime,
            $endtime,
        ];
        $var = array_merge($interviewerIds, $var);
        return Db::select($sql, $var);
    }

    /**求职者视频录像
     * @param $jobseekerId
     * @return array
     */
    public function jobseekerVideo($jobseekerId) {
        $sql = 'select projectid from RoomJobseeker where jobseekerid = ?';
        $projectId = Db::selectOne($sql, [$jobseekerId]);
        if (!$projectId) {
            return [];
        }
        if (!$this->chkInterviewerRight($projectId['projectid'])) {
            return [];
        }
        $sql = 'select filename,type,uuid,starttime,endtime,mediaurl from RoomJobseekerVideo where jobseekerid = ? AND type=? and filename like ? order by starttime';
        return Db::select($sql, [$jobseekerId, 'm3u8', '%mixed%']);
    }

    /**
     * 获取视频面试路线存放路径
     * @param $jobSeekerId
     * @return array
     */
    public function getVideoPathByUserId($jobSeekerId) {
        $sql = 'select uuid,filename,type,starttime,endtime from RoomJobseekerVideo where jobseekerid = ? AND type=? and filename like ? order by starttime';
        return Db::select($sql, [$jobSeekerId, 'm3u8', '%mixed%']);
    }

    /**
     * 获取视频面试路线存放路径
     * @param $uuid
     * @return array
     */
    public function getVideoPathById($uuid) {
        $sql = 'select uuid,filename,type,starttime,endtime from RoomJobseekerVideo where uuid = ? and type=? and filename like ?';
        return Db::select($sql, [$uuid, 'm3u8', '%mixed%']);
    }
}